package com.gugee.tiktok.common.model.dal.music.impl;

import cn.hutool.core.date.DateUtil;
import com.gugee.tiktok.common.model.dal.music.MusicExtRepository;
import com.gugee.tiktok.common.model.mysql.Aweme;
import com.gugee.tiktok.common.model.mysql.Challenge;
import com.gugee.tiktok.common.model.mysql.Music;
import com.gugee.tiktok.common.model.spider.aweme.Aweme_list;
import com.xiguaji.boot.toolkit.DateTimeUtils;
import com.xiguaji.boot.toolkit.jdbc.JdbcTemplateHelper;
import lombok.val;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;

public class MusicExtRepositoryImpl implements  MusicExtRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public List<Music> findByMid(List<Music> musics) {
        String sql = "SELECT * FROM music WHERE mid in ({mids})";
        String mids = "";
        for (Music music : musics) {
            if(StringUtils.isEmpty(mids)){
                mids += music.getMid();
                continue;
            }
            mids += ","+music.getMid();
        }
        sql = sql.replace("{mids}",mids);
        return JdbcTemplateHelper.findEntity(Music.class,sql);
    }

    @Override
    public Map<String,Object> getBelongRegion(long mid,int day){
        StringBuilder sql = new StringBuilder();
        val startDay = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        val endDay = LocalDateTime.now().minusDays(day).format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        sql.append("select a.o_region as region,count(*) as count ");
        sql.append("from aweme a ");
        sql.append("where a.o_music_id = ? ");
        sql.append("and a.datecode BETWEEN ? and ? ");
        sql.append("group by region order by count desc limit 0,1");
        Map<String, Object> map = JdbcTemplateHelper.selectFirst(sql.toString(),mid,Integer.valueOf(endDay),Integer.valueOf(startDay));
        return map;
    }

    @Override
    public List<Map<String,Object>> getBelongType(long mid,int day){
        StringBuilder sql = new StringBuilder();
        val startDay = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        val endDay = LocalDateTime.now().minusDays(day).format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        sql.append("select a.blogger_type as type,count(*) as count ");
        sql.append("from aweme a ");
        sql.append("where a.o_music_id = ? ");
        sql.append("and a.datecode BETWEEN ? and ? ");
        sql.append("group by type order by count desc");
        List<Map<String, Object>> mapList = JdbcTemplateHelper.select(sql.toString(),mid,Integer.valueOf(endDay),Integer.valueOf(startDay));
        return mapList;
    }
    @Override
    public void batchSaveMusicFromAwemes(List<Aweme_list> awemes) {
        long seconds = DateTimeUtils.currentTimeSecond();
        /**
         * todo 收录音乐，若想解耦，可将mid发送到消息队列批量消费
         */
        jdbcTemplate.batchUpdate(
                "INSERT INTO `tiktok`.`music`" +
                        "(`id`, `mid`," +
                        " `create_time`, " +
                        "`update_time`, `create_by`, `update_by`, `date_code`)" +
                        " VALUES(?,?,?,?,?,?,?)"
                , new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        Aweme_list aweme = awemes.get(i);
                        preparedStatement.setLong(1, 0);
                        preparedStatement.setLong(2, aweme.getMusic().getMid());
                        preparedStatement.setLong(3,seconds);
                        preparedStatement.setLong(4, seconds);
                        preparedStatement.setString(5, "batch save");
                        preparedStatement.setString(6, "batch save");
                        preparedStatement.setInt(7, Integer.parseInt(DateUtil.format(DateTimeUtils.localDateTimeBySecond(aweme.getCreate_time()),"yyyyMMdd")));
                    }

                    @Override
                    public int getBatchSize() {
                        return awemes.size();
                    }
                });
    }

    public void batchSaveMusicFromAwemes2(List<Aweme> awemes) {

        /**
         * todo 收录音乐，若想解耦，可将mid发送到消息队列批量消费
         */
        jdbcTemplate.batchUpdate(
                "INSERT INTO `tiktok`.`music`" +
                        "(`id`, `mid`," +
                        " `create_time`, " +
                        "`update_time`, `create_by`, `update_by`, `date_code`)" +
                        " VALUES(?,?,?,?,?,?,?)"
                , new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        Aweme aweme = awemes.get(i);
                        preparedStatement.setLong(1, 0);
                        preparedStatement.setLong(2, aweme.getOMusicId());
                        preparedStatement.setLong(3, aweme.getOCreateTime());
                        preparedStatement.setLong(4, aweme.getOCreateTime());
                        preparedStatement.setString(5, "batch save");
                        preparedStatement.setString(6, "batch save");
                        preparedStatement.setInt(7, aweme.getDatecode());
                    }

                    @Override
                    public int getBatchSize() {
                        return awemes.size();
                    }
                });
    }
    @Override
    public void batchUpdate(List<Music> musics) {
        long seconds = DateTimeUtils.currentTimeSecond();

        JdbcTemplateHelper.getJdbcTemplate().batchUpdate(
                "UPDATE `tiktok`.`music` SET  " +
                        "`author` = ?, `uid` = ?, `author_deleted` = ?, `title` = ?, `user_growth` = ?, `user_count` = ?," +
                        " `duration` = ?, `is_original` = ?, `offline_desc` = ?, `cover_medium` = ?, `region` = ?, " +
                        "`play_url` = ?, `share_url` = ?, `qr_code` = ?, `top_videos` = ?, `day_video_growth` = ?, `update_time` = ?," +
                        " `update_by` = ?" +
                        " WHERE `id` = ?"
                , new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        Music music = musics.get(i);
                        preparedStatement.setString(1, music.getAuthor());
                        preparedStatement.setLong(2, music.getUid());
                        preparedStatement.setBoolean(3, music.getAuthorDeleted());
                        preparedStatement.setString(4, music.getTitle());
                        preparedStatement.setLong(5, music.getUserGrowth());
                        preparedStatement.setLong(6, music.getUserCount());
                        preparedStatement.setInt(7, music.getDuration());
                        preparedStatement.setBoolean(8, music.getIsOriginal());
                        preparedStatement.setString(9, music.getOfflineDesc());
                        preparedStatement.setString(10, music.getCoverMedium());
                        preparedStatement.setString(11, music.getRegion());
                        preparedStatement.setString(12, music.getPlayUrl());
                        preparedStatement.setString(13, music.getShareUrl());
                        preparedStatement.setString(14, music.getQrCode());
                        preparedStatement.setString(15, music.getTopVideos());
                        preparedStatement.setString(16, music.getDayVideoGrowth());
                        preparedStatement.setLong(17, music.getUpdateTime());
                        preparedStatement.setString(18, music.getUpdateBy());
                        preparedStatement.setLong(19, music.getId());

                    }
                    @Override
                    public int getBatchSize() {
                        return musics.size();
                    }
                });
    }

    @Override
    public void batchInsert(List<Music> musics) {

        JdbcTemplateHelper.getJdbcTemplate().batchUpdate(
                "INSERT INTO `tiktok`.`music`(" +
                        " `mid`, `author`, `uid`, `author_deleted`, `title`, `user_growth`," +
                        " `user_count`, `duration`, `is_original`, `offline_desc`, `cover_medium`, `region`," +
                        " `play_url`, `share_url`, `qr_code`, `top_videos`, `day_video_growth`, `date_code`," +
                        " `create_time`, `update_time`, `create_by`, `update_by`)" +
                        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                , new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                        Music music = musics.get(i);
                        preparedStatement.setLong(1, music.getMid());
                        preparedStatement.setString(2, music.getAuthor());
                        preparedStatement.setLong(3, music.getUid());
                        preparedStatement.setBoolean(4, music.getAuthorDeleted());
                        preparedStatement.setString(5, music.getTitle());
                        preparedStatement.setLong(6, music.getUserGrowth());
                        preparedStatement.setLong(7, music.getUserCount());
                        preparedStatement.setInt(8, music.getDuration());
                        preparedStatement.setBoolean(9, music.getIsOriginal());
                        preparedStatement.setString(10, music.getOfflineDesc());
                        preparedStatement.setString(11, music.getCoverMedium());
                        preparedStatement.setString(12, music.getRegion());
                        preparedStatement.setString(13, music.getPlayUrl());
                        preparedStatement.setString(14, music.getShareUrl());
                        preparedStatement.setString(15, music.getQrCode());
                        preparedStatement.setString(16, music.getTopVideos());
                        preparedStatement.setString(17, music.getDayVideoGrowth());
                        preparedStatement.setInt(18, Integer.parseInt(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"))));
                        preparedStatement.setLong(19, music.getCreateTime());
                        preparedStatement.setLong(20, music.getUpdateTime());
                        preparedStatement.setString(21, music.getCreateBy());
                        preparedStatement.setString(22, music.getUpdateBy());

                    }
                    @Override
                    public int getBatchSize() {
                        return musics.size();
                    }
                });
    }


    @Override
    public List<Map<String, Object>> getRegionDistribution(long musicId, long beforeDaySecond) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) as count,r.name as name ");
        sql.append("from aweme a left join region r on a.o_region = r.code ");
        sql.append("where a.o_music_id = ? and a.o_create_time > ? ");
        sql.append("GROUP BY a.o_region ORDER BY count desc ");
        List<Map<String, Object>> mapList = JdbcTemplateHelper.select(sql.toString(),musicId,beforeDaySecond);
        return mapList;
    }

    @Override
    public List<Map<String, Object>> getIndustryDistribution(long musicId, long beforeDaySecond) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) as count,a.blogger_type as name ");
        sql.append("from aweme a ");
        sql.append("where a.o_music_id = ? and a.o_create_time > ? ");
        sql.append("GROUP BY a.blogger_type ORDER BY count desc ");
        List<Map<String, Object>> mapList = JdbcTemplateHelper.select(sql.toString(),musicId,beforeDaySecond);
        return mapList;
    }
}
